package ua.epam.muzalevskiy.traction.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import org.apache.log4j.Logger;
import ua.epam.muzalevskiy.traction.dao.exceptions.DatabaseException;
import ua.epam.muzalevskiy.traction.dao.exceptions.NotExistsParameterException;
import ua.epam.muzalevskiy.traction.dao.exceptions.NotUniqueParameterException;
import ua.epam.muzalevskiy.traction.route.Route;
import ua.epam.muzalevskiy.traction.vehicle.Vehicle;
import ua.epam.muzalevskiy.traction.vehicle.VehicleFactory;
import ua.epam.muzalevskiy.traction.vehicle.VehicleType;

/**
 *
 * @author Muzalevskiy Vitaliy
 */
public class VehicleDaoImplementation implements VehicleDao {

    private static final Logger LOGGER =
            Logger.getLogger(UserDaoImplementation.class);
    private final ConnectionFactory factory =
            ConnectionFactoryFactory.newConnectionFactory();
    
    /** Query for inserting new vehicle to DataBase.*/
    public static final String ADD_VEHICLE_SQL = "INSERT INTO "
            + "traction.vehicle_storage VALUES(?, ?, ?, ?, ?)";
    
    /** Query to select all vehicles from DataBase.*/
    public static final String SELECT_ALL_SQL = "SELECT * FROM "
            + "traction.vehicle_storage";
    
    /** Query to select all vehicles from DataBase which are free.*/
    public static final String SELECT_ALL_RESERVED_SQL = "SELECT * FROM "
            + "traction.vehicle_storage WHERE assigned_to_route = false" ;
    
    /** Query to select vehicle with concrete ID value.*/
    public static final String SELECT_BY_ID_SQL = "SELECT * FROM "
            + "traction.vehicle_storage WHERE id = ?";
    
    /** Query to select all vehicles of tram type from DataBase.*/
    public static final String SELECT_BUSES_SQL = "SELECT * FROM "
            + "traction.vehicle_storage WHERE vehicle_type = 1";
    
    /** Query to select all vehicles of tram type from DataBase.*/
    public static final String SELECT_TROLLEYBUSES_SQL = "SELECT * FROM "
            + "traction.vehicle_storage WHERE vehicle_type = 2";
    
    /** Query to select all vehicles of tram type from DataBase.*/
    public static final String SELECT_TRAMS_SQL = "SELECT * FROM "
            + "traction.vehicle_storage WHERE vehicle_type = 3";
    
    /** Query to delete vehicle with concrete ID value.*/
    public static final String DELETE_BY_ID_SQL = "DELETE FROM "
            + "traction.vehicle_storage WHERE id = ?";
    
    /** Query to update type of vehicle with concrete ID value.*/
    public static final String UPDATE_ROUTE_BY_ID_SQL = "UPDATE "
            + "traction.vehicle_storage SET assigned_to_route = true, "
            + "route_id = ? WHERE id = ?";
    
    public static final String ATTRIBUTE_ID = "id";
    public static final String ATTRIBUTE_VEHICLE_NUMBER = "vehicle_number";
    public static final String ATTRIBUTE_VEHICLE_TYPE = "vehicle_type";
    public static final String ATTRIBUTE_ASSIGNED_TO_ROUTE = "assigned_to_route";
    public static final String ATTRIBUTE_ROUTE_ID = "route_id";

    static {
        ConnectionFactoryFactory.setType(ConnectionFactoryFactory.FactoryType.C3P0);
    }

    /**
     * Method need to save information about new vehicle in DataBase.
     * 
     * @param query <code>PreparedStatement</code> value that include all
     *      information that need to be saved in DataBase.
     * @throws SQLException if there was some problems with Query.
     */
    private void doInsert(PreparedStatement query, int vehicleID, String vehicleNumber,
            int vehicleType) throws SQLException {
        query.setInt(1, vehicleID);
        query.setString(2, vehicleNumber);
        query.setInt(3, vehicleType);
        query.setBoolean(4, false);
        query.setObject(5, null);
        query.executeUpdate();
        LOGGER.info("New Vehicle info was put to DataBase! (vehicleID = "
                + vehicleID + ")");
    }

    /**
     * Method for getting information about vehicle from DataBase.
     * 
     * @param result <code>ResultSet</code> value of concrete query.
     * @return <code>Vehicle</code> value.
     * @throws SQLException if there was some problems with Query.
     */
    private Vehicle createVehicle(ResultSet result) throws SQLException {
        Vehicle vehicle = null;
        int vehicleID = result.getInt(ATTRIBUTE_ID);
        String  vehicleNum = result.getString(ATTRIBUTE_VEHICLE_NUMBER);
        int vehicleType = result.getInt(ATTRIBUTE_VEHICLE_TYPE);
        boolean assignedToRoute = result.getBoolean(ATTRIBUTE_ASSIGNED_TO_ROUTE);
        int routeNumber = result.getInt(ATTRIBUTE_ROUTE_ID);
        switch (vehicleType) {
            case 1:
                vehicle = new VehicleFactory()
                        .createVehicle(vehicleID, vehicleNum, VehicleType.BUS);
                vehicle.setAssignedToRoute(assignedToRoute);
                vehicle.setRouteID(routeNumber);
                break;
            case 2:
                vehicle = new VehicleFactory()
                        .createVehicle(vehicleID, vehicleNum, VehicleType.TROLLEYBUS);
                vehicle.setAssignedToRoute(assignedToRoute);
                vehicle.setRouteID(routeNumber);
                break;
            case 3:
                vehicle = new VehicleFactory()
                        .createVehicle(vehicleID, vehicleNum, VehicleType.TRAM);
                vehicle.setAssignedToRoute(assignedToRoute);
                vehicle.setRouteID(routeNumber);
                break;
            default:
                throw new SQLException();
        }
        return vehicle;
    }

    /**
     * Method need to get some list of vehicles.
     * @param sql <code>String</code> value of query with needed parameters.
     * @return <code>List</code> value with needed vehicles.
     */
    private List<Vehicle> selectListOfVehicles(String sql) {
        List<Vehicle> list = new LinkedList();
        Vehicle vehicle = null;
        Connection myConnection = null;
        try {            
            try {
                myConnection = factory.newConnection();
                Statement statement = myConnection.createStatement();
                ResultSet result = statement.executeQuery(sql);
                while (result.next()) {
                    vehicle = createVehicle(result);
                    list.add(vehicle);
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return list;
    }

    /**
     * Current method transfer <code>VehicleType</code> value to equals
     *      <code>int</code> value.
     * @param vehicle concrete <code>Vehicle</code> value.
     * @return <code>int</code> value equals to current type.
     */
    private int vehicleTypeToInt(Vehicle vehicle) {
        int type = 0;
        if (vehicle.getType().equals(VehicleType.BUS)) {
            type = 1;
        } else if (vehicle.getType().equals(VehicleType.TROLLEYBUS)) {
            type = 2;
        } else if (vehicle.getType().equals(VehicleType.TRAM)) {
            type = 3;
        }
        return type;
    }

    @Override
    public void addVehicle(Vehicle vehicle)
            throws NotUniqueParameterException {
        int type = vehicleTypeToInt(vehicle);
        Connection myConnection = null;
        try {           
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(ADD_VEHICLE_SQL);
                myConnection.setAutoCommit(false);
                if (selectVehicleByID(vehicle.getVehicleID()) != null) {
                    throw new NotUniqueParameterException();
                }
                try {
                    doInsert(query, vehicle.getVehicleID(),
                            vehicle.getVehicleNumber(), type);
                    myConnection.commit();
                } catch (SQLException ex) {
                    LOGGER.warn(ex.getMessage());
                    LOGGER.info("Transaction is rolled back!");
                    myConnection.rollback();
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
    }

    @Override
    public List<Vehicle> selectAll() {
        return selectListOfVehicles(SELECT_ALL_SQL);
    }
    
    @Override
    public List<Vehicle> selectAllReserved() {
        return selectListOfVehicles(SELECT_ALL_RESERVED_SQL);
    }

    @Override
    public Vehicle selectVehicleByID(int vehicleID) {
        Vehicle vehicle = null;
        Connection myConnection = null;
        try {            
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(SELECT_BY_ID_SQL);
                query.setInt(1, vehicleID);
                ResultSet result = query.executeQuery();
                while (result.next()) {
                    vehicle = createVehicle(result);
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return vehicle;
    }

    @Override
    public List<Vehicle> selectAllBuses() {
        return selectListOfVehicles(SELECT_BUSES_SQL);
    }

    @Override
    public List<Vehicle> selectAllTrolleybuses() {
        return selectListOfVehicles(SELECT_TROLLEYBUSES_SQL);
    }

    @Override
    public List<Vehicle> selectAllTrams() {
        return selectListOfVehicles(SELECT_TRAMS_SQL);
    }

    @Override
    public void deleteVehicleByID(int vehicleID)
            throws NotExistsParameterException {
        Connection myConnection = null;
        try {            
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(DELETE_BY_ID_SQL);
                myConnection.setAutoCommit(false);
                Vehicle vehicle = selectVehicleByID(vehicleID);
                if (vehicle == null) {
                    throw new NotExistsParameterException();
                }
                query.setInt(1, vehicleID);
                query.executeUpdate();
                query.close();
                myConnection.commit();
                LOGGER.info("Vehicle info was cleared from DataBase! "
                        + "(vehicleNumber = " + vehicleID + ")");
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
    }

    @Override
    public void assigneToRoute(int vehicleID, int routeID)
            throws DatabaseException, NotExistsParameterException {
        RouteDao ruotesDao = new RouteDaoImplementation();
        Vehicle vehicle;
        Route route;
        Connection myConnection = null;
        try {          
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(UPDATE_ROUTE_BY_ID_SQL);
                myConnection.setAutoCommit(false);
                vehicle = selectVehicleByID(vehicleID);
                route = ruotesDao.selectRouteByID(routeID);
                if (vehicle == null) {
                    throw new NotExistsParameterException();
                }
                if (route == null) {
                    throw new NotExistsParameterException();
                }
                if (route.getVehicleType() != vehicle.getType()){
                    throw new DatabaseException();
                }
                query.setInt(1, routeID);
                query.setInt(2, vehicleID);
                query.executeUpdate();
                query.close();
                myConnection.commit();
                LOGGER.info("Vehicle info was changed! (vehicleNumber = "
                        + vehicleID + ")");
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        } 
    }
}
